﻿using MySql.Data.MySqlClient;
using NB.MGJ.Common.Utility;
using NB.MGJ.FrameWork;
using NB.MGJ.Model;
using NB.MGJ.Model.CodeManage;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Mvc;
using L.EntityFramework;

namespace NB.MGJ.Service
{
    public partial class ProductService : BaseService
    {
        private static BaseRepository _baseRepository=new BaseRepository();

        private readonly ManagerService _ManagerService = new ManagerService();
    
        //public ProductService()
        //{
        //    _baseRepository = new BaseRepository();
         //_ManagerService = new ManagerService();
        //    
        //}
         private BaseRepository<L.EntityModel.EntityContext> baseRepository2;
         public ProductService()
        {
            baseRepository2 = CreateBaseRepository<L.EntityModel.EntityContext>();
           
        }



       ///////////////////////产品////////////////////////////////

        #region 增

        /// <summary>
        /// 添加产品
        /// </summary>
        /// <param name="model">产品</param>
        /// <returns></returns>
        public int AddProduct(L.EntityModel.Product_Product model)
        {
            return baseRepository2.Insert<L.EntityModel.Product_Product>(model);
        }

        #endregion

        #region 删
        public void DelteProduct(int product)
        {
            _baseRepository.ExecuteSql<int>("DELETE FROM Product_Product WHERE Product_Product.ID = " + product);
        }
        #endregion

        #region  改

        public int UpdProduct(L.EntityModel.Product_Product model)
        {
            return baseRepository2.Update<L.EntityModel.Product_Product>(model);
        }


        #endregion

        #region  查

        public PagedList<Product_Product> GetProductList(DetailSearch Smodel, ref PagerModel pager, LogOnUserModel user)
        {

            pager.FldName = @" * ";
            pager.TblName = @"(select a.*,b.EnterpriseName,c.TypeName from product_product as a LEFT JOIN organization_enterprise as b ON b.ID=a.EnterpriseId left join product_producttypes as c on a.TypeId=c.ID) z";

   

             string searchSql=" 1=1 ";

            if (Smodel.EnterpriseName != null)
            {
                searchSql += "  and (EnterpriseName like '%" + Smodel.EnterpriseName + "%')";
            }
            if (Smodel.ProductName != null)
            {
                searchSql += "  and (ProductName like '%" + Smodel.ProductName + "%')";
            }
            if (Smodel.TypeId != 0)
            {
                searchSql += " and (TypeId =  " + Smodel.TypeId + ")";
            }

            if (Smodel.StartDate != "" && Smodel.StartDate != null)
            {
                searchSql += " and  date_format(CreateDate,'%Y-%m-%d')= '" + Smodel.StartDate + "' ";

    
            }

            if (user.EnterpriseId == 1)
            {
             searchSql += " AND ViewDel=0 ";
            }
            else
            {
                var sonEnterprise = _ManagerService.GetSonEnterpriseStrArray(user.EnterpriseId);
                searchSql += " AND ViewDel=0 and  EnterpriseId in (" + sonEnterprise + ")";
            }


            pager.StrCondition = searchSql;
           
            pager.FldSort = " ChangeDate desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<Product_Product> resultList = _baseRepository.ExecuteStoredProcedureCollection<Product_Product>("Common_PageList", parameters);
            return new PagedList<Product_Product>(resultList, pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }

        //产品类型下拉
        public List<Product_ProductTypes> GetprotypeService(int EId)
        {
            string sql = "select ID,TypeName from  Product_ProductTypes where ViewDel=0";
            return _baseRepository.ExecuteSqlCollection<Product_ProductTypes>(sql).ToList();
        }
        public Product_Product GetProModel(int Id)
        {
            string sql = @"select a.*,b.EnterpriseName from product_product a LEFT JOIN organization_enterprise b on a.EnterpriseId=b.ID where a.Id={0}";
            sql = string.Format(sql, Id);
            return _baseRepository.ExecuteText<Product_Product>(sql);
        }


        public string GetProductBatchName(int batchId)
        {
            var _Result = _baseRepository.GetSingle(string.Format("SELECT BatchName FROM product_batch WHERE ID = {0}", batchId));
            if (_Result != null)
            {
                return _Result.ToString();
            }
            return null;
        }

        public object UpdateModelDataForScanCode(AntiFake_Digitcode Model)
        {
            var ConnectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
            if (!string.IsNullOrWhiteSpace(ConnectionString))
            {
                using (MySqlConnection conn = new MySqlConnection(ConnectionString))
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandType = System.Data.CommandType.Text;
                        object EName = null, ProductName = null, ProductImage = null, BatchName = null;
                        if (Model.EnterpriseId != 0)
                        {
                            cmd.CommandText = string.Format("SELECT EnterpriseName FROM organization_enterprise WHERE ID ={0}", Model.EnterpriseId);
                            EName = cmd.ExecuteScalar();
                        }
                        if (Model.ProductId != 0)
                        {
                            cmd.CommandText = string.Format("SELECT ProductName,ImageUrl1 FROM product_product WHERE ID ={0}", Model.ProductId);
                            MySqlDataReader Reader = cmd.ExecuteReader();
                            while (Reader.Read())
                            {
                                ProductName = Reader["ProductName"];
                                ProductImage = Reader["ImageUrl1"];
                            }
                            Reader.Close();
                        }
                        if (Model.ProductBatchId != 0)
                        {
                            cmd.CommandText = string.Format("SELECT BatchName FROM product_batch WHERE ID ={0}", Model.ProductBatchId);
                            BatchName = cmd.ExecuteScalar();
                        }
                        return new
                        {
                            EnterpriseName = EName,
                            ProductName = ProductName,
                            ProductImage = ProductImage,
                            BatchName = BatchName
                        };
                    }
                }
            }
            return new
            {
                EnterpriseName = "",
                ProductName = "",
                ProductImage = "",
                BatchName = "",
            };
        }
        #endregion

        #region    其他


        /// <summary>
        ///  根据机构ID获取产品列表
        /// </summary>
        /// <param name="eid"></param>
        /// <returns></returns>
        public List<Product_Product> GetProductListByEID(int eid)
        {

            var sonEnterprise = _ManagerService.GetSonEnterpriseStrArray(eid);
            var  searchSql = " AND ViewDel=0 and  EnterpriseId in (" + sonEnterprise + ")";
            string sql = "select ID, ViewDel, EnterpriseId, SaleStatus, ClassifyId, TempletId, TypeId, ProductName, Sequence, ShortDescription, ImageUrl1, ImageUrl2, ImageUrl3, ImageUrl4, ImageUrl5, AttributeValueId, MarketPrice, PresentPrice, TotalInventory, ProductCode, VistiCounts, SaleCounts, ShowSaleCounts, CubicMeter, FreightWeight, FreightPrice, PcDescription, MobileDescription, TaobaoProductId, Source, CreateLoginName, CreateDate, ChangeLoginID, ChangeLoginName, ChangeDate, TaoBaoUrl, WXUrl, TimeToMarket, Approve, Constitution, Subheading, ExpirationDate from Product_Product where 1=1 " + searchSql;
            return _baseRepository.ExecuteSqlCollection<Product_Product>(sql).ToList();
        }

        /// <summary>
        /// 根据机构ID获取该机构产品列表【下拉列表】
        /// </summary>
        /// <param name="productId">选中的产品ID</param>
        /// <param name="eid">机构ID</param>
        /// <returns></returns>
        public List<SelectListItem> GetProductSelectList(int productId, int eid)
        {
            List<Product_Product> result = GetProductListByEID(eid);
            List<SelectListItem> items = new List<SelectListItem>();
            
           
                items.Add(new SelectListItem() { Text = "--请选择--", Value = "" });
            
            
            for (int i = 0; i < result.Count; i++)
            {
                SelectListItem item = new SelectListItem()
                {
                    Text = result[i].ProductName,
                    Value = result[i].Id.ToString()
                };
                if (productId == result[i].Id)
                {

                    item.Selected = true;
                }
                items.Add(item);
            }
            return items;
        }

        #endregion



        ///////////////////产品分类////////////////////////


        #region 增
        public void AddProductType(L.EntityModel.Product_ProductTypes model)
        {
            baseRepository2.Insert<L.EntityModel.Product_ProductTypes>(model);
        }


        #endregion

        #region 删

        public void DeleteProductType(int id)
        {
            _baseRepository.ExecuteSql<int>("DELETE FROM Product_ProductTypes WHERE ID = " + id);
        }

        #endregion

        #region 改
        public void EditProductType(L.EntityModel.Product_ProductTypes model)
        {
            baseRepository2.Update<L.EntityModel.Product_ProductTypes>(model);
        }


        #endregion

        #region 查

        public PagedList<Product_ProductTypes> GetProductTypeList(string prosearch, PagerModel pager, LogOnUserModel user)
        {

            pager.FldName = @" * ";
            pager.TblName = @"(select a.*,b.EnterpriseName from product_producttypes as a LEFT JOIN organization_enterprise as b ON b.ID=a.EnterpriseId) z";
            pager.StrCondition = " EnterpriseId =" + user.EnterpriseId + " and ViewDel=0 ";
            if (!string.IsNullOrWhiteSpace(prosearch))
            {
                pager.StrCondition += "  And  TypeName  LIKE  '%" + prosearch + "%'  ";
            }
            pager.FldSort = " ID  desc";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<Product_ProductTypes> resultList = _baseRepository.ExecuteStoredProcedureCollection<Product_ProductTypes>("Common_PageList", parameters);
            return new PagedList<Product_ProductTypes>(resultList, pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }

        public Product_ProductTypes GetProductType(int id)
        {
            return _baseRepository.ExecuteSqlCollection<Product_ProductTypes>("SELECT * FROM Product_ProductTypes WHERE ID = " + id).FirstOrDefault();
        }

        #endregion

        #region  其他

        public IEnumerable<Product_ProductTypes> GetProductTypeListBox(int eid)
        {
            string sql = "select * from Product_ProductTypes  where ViewDel=0  and  EnterpriseId=" + eid;
            IEnumerable<Product_ProductTypes> lsit = _baseRepository.ExecuteSqlCollection<Product_ProductTypes>(sql);
            return lsit;
        }


        public List<SelectListItem> GetProductTypeSelectList(int productTypeId,int eid)
        {
            List<Product_ProductTypes> result = GetProductTypeListBox(eid).ToList();
            List<SelectListItem> items = new List<SelectListItem>();
            SelectListItem it = new SelectListItem()
            {
                Text = "--请选择--",
                Value = "0"
            };
            items.Add(it);
            for (int i = 0; i < result.Count; i++)
            {

                SelectListItem item = new SelectListItem()
                {
                    Text = result[i].TypeName,
                    Value = result[i].Id.ToString()
                };
                if (productTypeId != null)
                {
                    if (productTypeId == result[i].Id)
                    {
                        item.Selected = true;
                    }
                }
                items.Add(item);
            }
            return items;
        }


        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="qsearch"></param>
        /// <returns></returns>
        public IEnumerable<Product_ProductTypes> Getproducttype(string key)
        {
            var resule = "select ID, ViewDel, EnterpriseId, TypeName, Remark, Sequence, ChangeLoginID, ChangeLoginName, ChangeDate from Product_ProductTypes where ViewDel=0";
            if (!string.IsNullOrWhiteSpace(key))
            {
                resule += " and ID in (" + key +")";
            }
            return _baseRepository.ExecuteSqlCollection<Product_ProductTypes>(resule);
        }


        #endregion







        //////////////////////////产品目录///////////////////



        public List<V_Tree> GetClassifyTreeByEID()
        {
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append("select a.ID  as ID,a.ClassifyName as Name, a.FatherId as PID, -1 as ProductId from Product_Classify as a where 1=1");
            List<V_Tree> tree = _baseRepository.ExecuteSqlCollection<V_Tree>(strSql1.ToString()).ToList();
            return tree;
        }

        /// <summary>
        /// 根据父ID获取目录    父ID为0为1级目录   其他为子目录
        /// </summary>
        /// <param name="fatherId">父ID</param>
        /// <returns></returns>
        public List<V_Tree> GetClassifyByFatherId(int fatherId = 0)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.ID  as ID,a.ClassifyName as Name, a.FatherId as PID, -1 as ProductId from Product_Classify as a where FatherId="+fatherId);
            List<V_Tree> tree = _baseRepository.ExecuteSqlCollection<V_Tree>(strSql.ToString()).ToList();
            return tree;
        }


        public List<V_Tree> GetClassifyByFatherIds(string fatherIds)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select a.ID  as ID,a.ClassifyName as Name, a.FatherId as PID, -1 as ProductId from Product_Classify as a where FatherId in (" + fatherIds+")");
            List<V_Tree> tree = _baseRepository.ExecuteSqlCollection<V_Tree>(sb.ToString()).ToList();
            return tree;
        }
    
    





        public List<SelectListItem> GetClassifySelectList(int classifyId,int fatherId)
        {
            List<V_Tree> result = GetClassifyByFatherId(fatherId);
            List<SelectListItem> items = new List<SelectListItem>();
            items.Add(new SelectListItem()
            {
                Text = "",
                Value = ""
            });
            for (int i = 0; i < result.Count; i++)
            {
                SelectListItem item = new SelectListItem()
                {
                    Text = result[i].Name,
                    Value = result[i].ID.ToString()
                };
                if (classifyId == result[i].ID)
                {

                    item.Selected = true;
                }
                items.Add(item);
            }
            return items;
        }

        public List<SelectListItem> GetClassifySelectList(int classifyId, string fatherIds)
        {
            List<V_Tree> result = GetClassifyByFatherIds(fatherIds);
            List<SelectListItem> items = new List<SelectListItem>();
            items.Add(new SelectListItem()
            {
                Text = "",
                Value = ""
            });


            for (int i = 0; i < result.Count; i++)
            {
                if (classifyId == 0 && i == 0)
                {
                    SelectListItem item = new SelectListItem()
                    {
                        Text = result[i].Name,
                        Value = result[i].ID.ToString()
                    };
                    item.Selected = true;
                    items.Add(item);
                }
                else
                {
                    SelectListItem item = new SelectListItem()
                    {
                        Text = result[i].Name,
                        Value = result[i].ID.ToString()
                    };
                    if (classifyId == result[i].ID)
                    {

                        item.Selected = true;
                    }
                    items.Add(item);

                }
            }
            return items;
        }





    }
}
